What is a CSV file?

Files with .csv (Comma Separated Values) extension represent plain text files that contain records of data with comma separated values. Each line in a CSV file is a new record from the set of records contained in the file. Such files are generated when data transfer is intended from one storage system to another. Since all applications can recognize records separated by comma, import of such data files to database is done very conveniently. Almost all spreadsheet applications such as Microsoft Excel or OpenOffice Calc can import CSV without much effort. Data imported from such files is arranged in cells of a spreadsheet for representation to user.

“CSV” refers to the file extension for “comma-separated value” files, commonly used by spreadsheet programs. These files store data as text separated by commas, which spreadsheet applications convert into table cells for easier reading and editing.

Brief History

Following are some quick facts about the origin and history of CSV file format.

  • 1972 - IBM Fortran (level H extended) compiler supported them under OS/360

  • 1978 - List-directed input/output was supported by FORTRAN 77 that used commas and spaces for delimiters

  • 2005 - CSV was standardized with RFC4180 as a MIME Content Type.

  • 2013 - RFC4180’s deficiencies were handled by a W3C recommendation

  • 2015 - W3C made the first drafts of recommendations for CSV-metadata standards, that began as recommendation in December 2015

Convert CSV Files

CSV files can be converted to several different file formats using the applications that can open these files. For example, Microsoft Excel can import data from CSV file format and save it to XLS, XLSX, PDF, TXT, XML and HTML file formats. Similarly, other desktop as well as online services provide the capability to export CSV files to HTML, ODS and RTF.

CSV File Format

CSV file format is known to be specified under RFC4180. It defines any file to be CSV compliant if:

  • Each record is located on a separate line, delimited by a line break (CRLF).  For example:
    • aaa,bbb,ccc CRLF
    • zzz,yyy,xxx CRLF
  • The last record in the file may or may not have an ending line break.  For example:
    • aaa,bbb,ccc CRLF
    • zzz,yyy,xxx
  • There may be an optional header line appearing as the first line of the file with the same format as normal record lines.  This header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file (the presence or absence of the header line should be indicated via the optional “header” parameter of this MIME type).  For example:
    • field_name,field_name,field_name CRLF
    • aaa,bbb,ccc CRLF
    • zzz,yyy,xxx CRLF
  • Within the header and each record, there may be one or more fields, separated by commas.  Each line should contain the same number of fields throughout the file.  Spaces are considered part  of a field and should not be ignored.  The last field in the record must not be followed by a comma.  For example:
    • aaa,bbb,ccc
  • Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all).  If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.  For example:\
    • “aaa”,“bbb”,“ccc” CRLF
    • zzz,yyy,xxx
  • Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.  For example:
    • “aaa”,“b CRLF
    • bb”,“ccc” CRLF
    • zzz,yyy,xxx
  • If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.  For example:
    • “aaa”,“b"“bb”,“ccc”

However, in light of modern usage, the delimiter is not limited to comma only and can be semicolon, tab or spaces as well. Applications such as Microsoft Excel provide option to specify the delimiter character for importing records from a CSV file.

How to open and edit a CSV file?

You can open and edit a CSV file using the following methods:

1. Using a Spreadsheet Application

  • Microsoft Excel:

    • Open Excel, go to File > Open, and select the CSV file.
    • Edit the data and save it back as CSV if needed.

  • Google Sheets:

    • Upload the CSV to Google Drive and open it with Google Sheets.
    • Edit and download it as CSV.

  • LibreOffice Calc:

    • Open the file in this free tool and make edits.

2. Using a Text Editor

  • Open the CSV in Notepad (Windows) or TextEdit (macOS).
  • Edit the data directly, but ensure to maintain the comma-separated structure.

3. Using Online Tools

  • Use online CSV editors to view and edit the file in your browser.

4. Using Code

  • Edit programmatically with languages like Python (using libraries like pandas) or Excel automation tools.

Save Carefully

When saving after editing, ensure the file remains in CSV format to retain compatibility.

How to create a CSV file?

You can create a CSV file using several methods:

1. Using a Spreadsheet Application

  • Microsoft Excel:

    • Enter your data in rows and columns.
    • Go to File > Save As, choose CSV (Comma delimited) (.csv), and save.

  • Google Sheets:

    • Enter data in a new sheet.
    • Go to File > Download > Comma-separated values (.csv).

2. Using a Text Editor

  • Open a text editor like Notepad (Windows) or TextEdit (macOS).
  • Enter data in a comma-separated format:
Name,Age,Location
John,25,New York
Jane,30,Los Angeles
  • Save the file with a .csv extension.

References

See Also